package com.ets.Tax_DAO;

import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import com.ets.common.DateUtils;
import com.ets.database.DB_Pipe;

/******************************************************************************* 
 The <big><b>TaxDB</b></big> class implements the reader & writer methods of
 the <b>TaxDAO</b> interface to provide the data access to the
 <big><b>Tax</b></big> table
 <br><br>
 @author D.Merkling - 1:33:38 PM - Aug 22, 2011
 <br><br>
 @version 1.2  
*******************************************************************************/ 
/*------------------------------------------------------------------------------
 The <big><b>TaxDB</b></big> class implements the reader & writer methods of
 the <b>TaxDAO</b> interface to provide the data access to the
 <big><b>Tax</b></big> table
 <br><br>
 @author D.Merkling - 1:33:38 PM - Aug 22, 2011
 <br><br>
 @version 1.2  
------------------------------------------------------------------------------*/
public class TaxDB extends DB_Pipe implements TaxDAO
  {
   public TaxDB()
	 {
	 }

   /*******************************************************************************
	Tax reader methods
   *******************************************************************************/
   
   public boolean existsTax (String state, String county, String city, Date effDt)
     {
	  String tmpState  = state.trim();
             tmpState  = tmpState.toUpperCase();
      String tmpCounty = county.trim();
             tmpCounty = tmpCounty.toUpperCase();
      String tmpCity   = city.trim();
             tmpCity   = tmpCity.toUpperCase();
 	  String exists    = "SELECT Rt "             +
                           "FROM tax "            +
                          "WHERE State  =  ? "    +
                            "AND County =  ? "    +
                            "AND City   =  ? "    +
                            "AND Dt_Eff <= ? "    + 
                          "ORDER BY State  ASC, " +
                                   "County ASC, " +
                                   "City   ASC, " +
                                   "Dt_Eff DESC";
      try
		{
         DB_Pipe              = DB_Connect();
		 PreparedStatement ps = DB_Pipe.prepareStatement(exists);
		 ps.setString(1, tmpState);
		 ps.setString(2, tmpCounty);
		 ps.setString(3, tmpCity);
		 ps.setDate(4, effDt);
		 ResultSet         rs  = ps.executeQuery();
		 if (rs.next())
		   {
		    rs.close();
		    ps.close();
		    return true;
		   }
		 else
		   return false;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return false;
		}
	  finally
		{
		 DB_Disconnect(DB_Pipe);
		}
	 }

   public Tax getTax (String state, String county, String city, Date effDt)
	 {
	  String sel = "SELECT State, "         +
                          "County, "        +
                          "City, "          +
                          "Dt_Eff, "        +
                          "Rt "             +
 	                 "FROM tax "            +
	                "WHERE State = ? "      +
	                  "AND County = ? "     +
	                  "AND City   = ? "     +
	                  "AND Dt_Eff <= ? "    + 
                    "ORDER BY State  ASC, " +
                             "County ASC, " +
                             "City   ASC, " +
                             "Dt_Eff DESC";
	  try
		{
	     DB_Pipe              = DB_Connect();
		 PreparedStatement ps = DB_Pipe.prepareStatement(sel);
		 ps.setString(1, state);
		 ps.setString(2, county);
		 ps.setString(3, city);
		 ps.setDate(4, effDt);
		 ResultSet         rs  = ps.executeQuery();
		 if (rs.next())
		   {
		    String State  = rs.getString("State");
		    String County = rs.getString("County");
		    String City   = rs.getString("City");
		    Date   Dt_Eff = rs.getDate("Dt_Eff");
		    double Rt     = rs.getDouble("Rt");
		    Tax    t      = new Tax(State, County, City, Dt_Eff, Rt);
			rs.close();
			ps.close();
		    return t;
		   }
		 else
		   return null;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();  // for debugging
		 return null;
		}
	  finally
		{
		 DB_Disconnect(DB_Pipe);
		}
	 }

   public boolean getTaxes (ArrayList<Tax> taxes)
	 {
	  String qry = "SELECT State, "         +
                          "County, "        +
                          "City, "          +
                          "Dt_Eff, "        +
                          "Rt "             +
 	                 "FROM tax "            +
	                "ORDER BY State  ASC, " +
                             "County ASC, " +
	                         "City   ASC, " +
	                         "Dt_Eff DESC";
	  try
		{
		 DB_Pipe              = DB_Connect();
		 PreparedStatement ps = DB_Pipe.prepareStatement(qry);
		 ResultSet         rs = ps.executeQuery();
		 while(rs.next())
		   {
		    String State  = rs.getString("State");
		    String County = rs.getString("County");
		    String City   = rs.getString("City");
		    Date   Dt_Eff = rs.getDate("Dt_Eff");
		    double Rt     = rs.getDouble("Rt");
		    Tax    t      = new Tax(State, County, City, Dt_Eff, Rt);
		    taxes.add(t);
		   }
		 rs.close();
		 ps.close();
		 return true;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();  // for debugging
		 return false;
		}
	  finally
		{
		 DB_Disconnect(DB_Pipe);
		}
	 }

   /*******************************************************************************
	Tax writer methods
   *******************************************************************************/
   
   public boolean addTax (Tax t)
	 {
	  String ins = "INSERT "                            +
	                 "INTO tax (State, "                +
	                           "County, "               +
	                           "City, "                 +
	                           "Dt_Eff, "               +
	                           "Rt, "                   +
	                           "TMS_Crtd, "             +
	                           "User_Crtd, "            +
	                           "TMS_Updt, "             +
	                           "User_Updt) "            +
			       "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
	  try
		{
		 DB_Pipe              = DB_Connect();
		 PreparedStatement ps = DB_Pipe.prepareStatement(ins);
	     Timestamp         ts = DateUtils.getTmstmp();
		 ps.setString(1, t.getState());
		 ps.setString(2, t.getCounty());
		 ps.setString(3, t.getCity());
		 ps.setDate(4, t.getDtEff());
		 ps.setDouble(5, t.getRt());
		 ps.setTimestamp(6, ts);
		 ps.setString(7, "USER0001");
		 ps.setTimestamp(8, ts);
		 ps.setString(9, "USER9999");
		 ps.executeUpdate();
		 ps.close();
		 return true;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return false;
		}
	  finally
		{
		 DB_Disconnect(DB_Pipe);
		}
	 }
   
   public boolean updateTax (Tax t)
	 {
	  String upd = "UPDATE tax "            +
                      "SET Rt        = ?, " +
                          "TMS_Updt  = ?, " +
			              "User_Updt = ? "  +
                    "WHERE State  =  ? "    +
                      "AND County =  ? "    +
                      "AND City   =  ? "    +
                      "AND Dt_Eff <= ? "; 
	  try
		{
		 DB_Pipe              = DB_Connect();
		 PreparedStatement ps = DB_Pipe.prepareStatement(upd);
	     Timestamp         ts = DateUtils.getTmstmp();
		 ps.setDouble(1, t.getRt());
		 ps.setTimestamp(2, ts);
		 ps.setString(3, "USER8888");
		 ps.executeUpdate();
		 ps.close();
		 return true;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
	 	 return false;
		}
	  finally
		{
		 DB_Disconnect(DB_Pipe);
		}
	 }

   public boolean deleteTax (Tax t)
	 {
	  String del = "DELETE "              +
	                 "FROM tax "          +
                    "WHERE State  =  ? "  +
                      "AND County =  ? "  +
                      "AND City   =  ? "  +
                      "AND Dt_Eff <= ? "; 
	  try
		{
		 DB_Pipe              = DB_Connect();
		 PreparedStatement ps = DB_Pipe.prepareStatement(del);
		 ps.setString(1, t.getState());
		 ps.setString(2, t.getCounty());
		 ps.setString(3, t.getCity());
		 ps.setDate(4, t.getDtEff());
		 ps.executeUpdate();
		 ps.close();
		 return true;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return false;
		}
	 }
  }
